/*
 * ExtTableDAO.java
 *
 * $Id: ExtTableDAO.java,v 1.1 2008-03-18 12:58:45 mario Exp $
 *
 * Author: Mario García García - Imagos 2007
 */
package org.ceteca.explica.core.extTables;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;

import org.ceteca.explica.core.InterfaceErrors;
import org.ceteca.explica.server.util.db.CriteriaBean;
import org.ceteca.explica.server.util.db.HibernateUtil;
import org.ceteca.explica.server.util.db.InterfaceDB;
import org.ceteca.explica.server.util.db.SQLGenerator;

/**
 * This class implements the table extension data access object. It's a part of the 
 * data access layer and gives access to the database data. It knows the database
 * structure and implementation. 
 * <br/>
 * This class is implemented to access a PostgreSQL database implementation.
 * <br/>
 * Responsibilities:
 * <li>
 * 	<ul> Provide statements for inserting extension tables definitions and values
 *  into the database. </ul>
 *  <ul> Provide statements for updating extension tables definitions and values
 *  into the database. </ul>
 *  <ul> Provide statements for deleting extension tables definitions and values
 *  from the database. </ul>
 *  <ul> Provide the database queries needed for extension tables. Those queries
 *  will return the extended table definition and the extension values for an
 *  extended item. </ul>
 * </li>
 */

public class ExtTableDAO {
	public ExtTableDAO() {
	}
	
	/**
	 * Query (by primary key) the database to get an extension table definition. 
	 * This definition includes the extended table parameters and the extended 
	 * fields defined for the extension table that has been queried.
	 * @param idExtTable long, Internal id of the extension table.
	 * @throws ExtTableException, in case any error take place during
	 * the query operation.
	 * @return ExtTableOT, extension table definition if it exists, null in 
	 * any other case.
	 */
	public ExtTableOT getExtensionTableDefinition(long idExtTable) throws ExtTableException {
		ExtTableOT result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Load the object by the primary key (idExtTable)
        	result = (ExtTableOT)session.get(ExtTableOT.class, new Long(idExtTable));

            System.out.println( "Consulta realizada! " +
            		result == null?(" No se ha encontrado ninguna tabla de extensión correspondiente!"):
            		("Se ha encontrado la tabla: " + result.getTableDescription()));
        }
        catch (HibernateException ex) {
        	System.err.println("No se puede realizar la consulta a la BD!");
        	ex.printStackTrace();
        	throw new ExtTableException("" + idExtTable, InterfaceErrors.ERR_NO_EXT_TABLE_FOUND);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query (by the extension table code) the database to get an extension table 
	 * definition. This definition includes the extended table parameters and the 
	 * extended fields defined for the extension table that has been queried.
	 * @param codExtTable String, External code of the extension table.
	 * @throws ExtTableException, in case any error take place during
	 * the query operation.
	 * @return ExtTableOT, extension table definition if it exists, null in 
	 * any other case.
	 */
	public ExtTableOT getExtensionTableDefinition(String codExtTable) throws ExtTableException{
		ExtTableOT result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Build the query statement
        	String sql = "FROM ExtTableOT t WHERE t.codExtTable=?";
        	Query q = session.createQuery(sql);
        	q.setString(0, codExtTable);

        	// Run the query statement
        	result = (ExtTableOT)q.uniqueResult();
        	
            System.out.println( "Consulta realizada! " +
            		(result == null?" No se ha encontrado ninguna tabla de extensión correspondiente!":
            		"Se ha encontrado la tabla: " + result.getTableDescription()));
        }
        catch (HibernateException ex) {
        	System.err.println("No se puede realizar la consulta a la BD!");
        	ex.printStackTrace();
        	throw new ExtTableException(codExtTable, InterfaceErrors.ERR_NO_EXT_TABLE_FOUND);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
		
	/**
	 * Query the database to get the extension values of an extended item. 
	 * @param idValue long, Value of the internal id of the extended item. It's
	 * the primary key of the extended item.
	 * @param codExtTable String, External code of the extension table.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 * @return Map, set of all the extension fields defined for the extension
	 * table containing all the values set for the extended item.
	 */
	public Map getExtendedItemValues(Long idValue, String codExtTable) throws ExtTableException {
		HashMap result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
        try {
        	
        	SQLGenerator sqlGen = new SQLGenerator();
        	
        	ArrayList lFields = this.getExtendedItemFields();
        	ArrayList lWhereCriteria = new ArrayList();
        	CriteriaBean crit = new CriteriaBean();
        	crit.setFieldId("\"" + InterfaceExtTable.EXT_TABLE_CODE_FIELDNAME + "\"");
        	crit.setFieldValue(codExtTable);
        	crit.setFieldOperator(InterfaceDB.SQL_OPERATOR_EQUAL);
        	crit.setFieldLogicOperator(InterfaceDB.LOGIC_OPERATOR_AND);
        	lWhereCriteria.add(crit);
        	
        	crit = new CriteriaBean();
        	crit.setFieldId("\"" + InterfaceExtTable.EXT_FIELD_VALUE_ITEM_ID_FIELDNAME + "\"");
        	crit.setFieldValue(idValue);
        	crit.setFieldOperator(InterfaceDB.SQL_OPERATOR_EQUAL);
        	crit.setFieldLogicOperator(InterfaceDB.LOGIC_OPERATOR_AND);
        	lWhereCriteria.add(crit);
        	
        	ArrayList lOrderCriteria = new ArrayList();
        	
        	String sql = sqlGen.getSQLQuery("\"" + InterfaceExtTable.EXT_FIELD_VALUE_VIEW_NAME + "\"", 
        			lFields, lWhereCriteria, lOrderCriteria);
        	
        	// Open a new SQL query to get the values for the extended item. It will use
        	// the SQL view VW_EXT_ITEM_VALUES.
	        SQLQuery sqlQ = session.createSQLQuery(sql);

	        // Add the types of the extension table selected fields.
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_ID_FIELDNAME, Hibernate.LONG);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME, Hibernate.STRING);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_TYPE_FIELDNAME, Hibernate.INTEGER);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_LENGTH_FIELDNAME, Hibernate.BIG_DECIMAL);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_DESCRIPTION_FIELDNAME, Hibernate.STRING);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_NULLABLE_FIELDNAME, Hibernate.BOOLEAN);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_HISTORIZABLE_FIELDNAME, Hibernate.BOOLEAN);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_ITEM_ID_FIELDNAME, Hibernate.LONG);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_STRINGVALUE_FIELDNAME, Hibernate.STRING);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_DATEVALUE_FIELDNAME, Hibernate.DATE);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_NUMBERVALUE_FIELDNAME, Hibernate.BIG_DECIMAL);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_BOOLEANVALUE_FIELDNAME, Hibernate.BOOLEAN);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_LONGVALUE_FIELDNAME, Hibernate.BLOB);
	        
	        // Add the transformer to load the results of the query into the Transfer Object specified. 
	        sqlQ.setResultTransformer(Transformers.aliasToBean(ExtFieldOT.class));
	        
	        // Set the values of the query parameters.
	        sqlQ.setString(0, codExtTable);
	        sqlQ.setLong(1, idValue.intValue());
	        
	        // Get the list of extension fields and values as the result of the query.
	        ArrayList l = (ArrayList)sqlQ.list();
	        System.out.println(l);

	        if (l != null && l.size() > 0)
	        	result = new HashMap();

	        // Iterate the list to get all the extension fields and values defined for the extended item 
	        for (int i=0; l != null && i<l.size(); i++) {
	        	ExtFieldOT extField = (ExtFieldOT)l.get(i);
	        	System.out.println(extField);
	        	System.out.println(extField.getCodExtField());
	        	
	        	// Add the extension field to the resulting map
	        	result.put(extField.getCodExtField(), extField);
	        }

            System.out.println( "Consulta realizada! ");
        }
        catch (HibernateException ex) {
        	System.err.println("No se puede realizar la consulta a la BD!");
        	ex.printStackTrace();
        	throw new ExtTableException(codExtTable, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query the database to get the extension field (definition and value) of 
	 * an extended item. 
	 * @param idValue long, Value of the internal id of the extended item. It's
	 * the primary key of the extended item.
	 * @param codExtField String, External code of the extension field.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 * @return ExtFieldOT, definition and value of the extension field queried.
	 * If there is no value assigned to the extended item for this field, then
	 * return only the extension field definition.
	 */
	public ExtFieldOT getExtensionField(Long idValue, String codExtField) throws ExtTableException {
		ExtFieldOT result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
        try {
        	SQLGenerator sqlGen = new SQLGenerator();
        	
        	ArrayList lFields = this.getExtendedItemFields();
        	ArrayList lWhereCriteria = new ArrayList();
        	CriteriaBean crit = new CriteriaBean();
        	crit.setFieldId("\"" + InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME + "\"");
        	crit.setFieldValue(codExtField);
        	crit.setFieldOperator(InterfaceDB.SQL_OPERATOR_EQUAL);
        	crit.setFieldLogicOperator(InterfaceDB.LOGIC_OPERATOR_AND);
        	lWhereCriteria.add(crit);
        	
        	crit = new CriteriaBean();
        	crit.setFieldId("\"" + InterfaceExtTable.EXT_FIELD_VALUE_ITEM_ID_FIELDNAME + "\"");
        	crit.setFieldValue(idValue);
        	crit.setFieldOperator(InterfaceDB.SQL_OPERATOR_EQUAL);
        	crit.setFieldLogicOperator(InterfaceDB.LOGIC_OPERATOR_AND);
        	lWhereCriteria.add(crit);
        	
        	ArrayList lOrderCriteria = new ArrayList();
        	
        	String sql = sqlGen.getSQLQuery("\"" + InterfaceExtTable.EXT_FIELD_VALUE_VIEW_NAME + "\"", 
        			lFields, lWhereCriteria, lOrderCriteria);
        	
        	// Open a new SQL query to get the values for the extended item. It will use
        	// the SQL view VW_EXT_ITEM_VALUES.
	        SQLQuery sqlQ = session.createSQLQuery(sql);
			
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_ID_FIELDNAME, Hibernate.LONG);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME, Hibernate.STRING);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_TYPE_FIELDNAME, Hibernate.INTEGER);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_LENGTH_FIELDNAME, Hibernate.BIG_DECIMAL);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_DESCRIPTION_FIELDNAME, Hibernate.STRING);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_NULLABLE_FIELDNAME, Hibernate.BOOLEAN);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_HISTORIZABLE_FIELDNAME, Hibernate.BOOLEAN);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_ITEM_ID_FIELDNAME, Hibernate.LONG);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_STRINGVALUE_FIELDNAME, Hibernate.STRING);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_DATEVALUE_FIELDNAME, Hibernate.DATE);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_NUMBERVALUE_FIELDNAME, Hibernate.BIG_DECIMAL);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_BOOLEANVALUE_FIELDNAME, Hibernate.BOOLEAN);
	        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_VALUE_LONGVALUE_FIELDNAME, Hibernate.BLOB);
	        
	        sqlQ.setResultTransformer(Transformers.aliasToBean(ExtFieldOT.class));
	        sqlQ.setString(0, codExtField);
	        sqlQ.setLong(1, (idValue == null?-1:idValue.intValue()));
	        
	        // Get the extension field definition and value
	        result = (ExtFieldOT)sqlQ.uniqueResult();
	        System.out.println(result);
	        
	        // If no extension field value was found for the extended item
	        if (result == null) {
	        	// Get the extension field definition
	        	lWhereCriteria = new ArrayList();
	        	crit = new CriteriaBean();
	        	crit.setFieldId("\"" + InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME + "\"");
	        	crit.setFieldValue(codExtField);
	        	crit.setFieldOperator(InterfaceDB.SQL_OPERATOR_EQUAL);
	        	crit.setFieldLogicOperator(InterfaceDB.LOGIC_OPERATOR_AND);
	        	lWhereCriteria.add(crit);
	        	
	        	lOrderCriteria = new ArrayList();
	        	
	        	lFields = this.getExtensionFieldDefinitionFields();
	        	
	        	sql = sqlGen.getSQLQuery("\"" + InterfaceExtTable.EXT_FIELD_TABLE_NAME + "\"", 
	        			lFields, lWhereCriteria, lOrderCriteria);
	        	
	        	sqlQ = session.createSQLQuery(sql);
				
		        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_ID_FIELDNAME, Hibernate.LONG);
		        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME, Hibernate.STRING);
		        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_TYPE_FIELDNAME, Hibernate.INTEGER);
		        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_LENGTH_FIELDNAME, Hibernate.BIG_DECIMAL);
		        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_DESCRIPTION_FIELDNAME, Hibernate.STRING);
		        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_NULLABLE_FIELDNAME, Hibernate.BOOLEAN);
		        sqlQ.addScalar(InterfaceExtTable.EXT_FIELD_HISTORIZABLE_FIELDNAME, Hibernate.BOOLEAN);
		        
		        sqlQ.setResultTransformer(Transformers.aliasToBean(ExtFieldOT.class));
		        sqlQ.setString(0, codExtField);
		        
		        // Get the extension field definition
		        result = (ExtFieldOT)sqlQ.uniqueResult();
		        System.out.println(result);
	        }
	        
            System.out.println( "Consulta realizada! ");
        }
        catch (HibernateException ex) {
        	System.err.println("No se puede realizar la consulta a la BD!");
        	ex.printStackTrace();
        	throw new ExtTableException(null, codExtField, idValue == null?-1:idValue.longValue(), InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Update or add a new extension field definition.
	 * @param extField ExtFieldOT, extended field to update/add.
	 * @throws ExtTableException, in case any error take place during
   	 * the update operation. 
	 */
	public void saveExtensionField(ExtFieldOT extField) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database update operation.
        Transaction tx = session.beginTransaction();
        
        // If the extension field is null --> Return error message
        if (extField == null || extField.getCodExtField() == null) {
            System.out.println("A null extension field is tried to be updated!");
            throw new ExtTableException(null, null, -1, InterfaceErrors.ERR_SAVE_NULL_EXT_FIELD);
        }
        
        // If the extension table is null --> Return error message
        if (extField != null && extField.getExtTable() == null) {
            System.out.println("An extension field not linked to any extension table is tried to be updated!");
            throw new ExtTableException(null, extField.getCodExtField(), -1, InterfaceErrors.ERR_SAVE_EXT_FIELD_WITH_NULL_EXT_TABLE);
        }

        try {
        	System.out.println(extField.getExtTable());
        	session.saveOrUpdate(extField);
        	System.out.println("Update done successfully!");
        	tx.commit();
        	session.flush();
        }
        catch (org.hibernate.StaleStateException he) {
        	tx.rollback();
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
    			he.toString());
          	throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_NO_SYNC, he);
        }
        catch (org.hibernate.exception.ConstraintViolationException cex) {
        	tx.rollback();
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
    			cex.toString());
        	throw new ExtTableException(cex.getConstraintName(), extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_DB_CONSTRAINT_VIOLATION, cex);
        }
        catch (Exception ex) {
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
       
        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Update the value of an extension field.
	 * @param extField ExtFieldOT, extended field to update.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void saveExtendedFieldValue(ExtFieldOT extField) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database update operation.
        Transaction tx = session.beginTransaction();

        try {
        	this.saveExtendedFieldValue(extField, session, tx);
        	tx.commit();
        	session.flush();
        }
        catch (org.hibernate.StaleStateException he) {
        	tx.rollback();
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
    			he.toString());
          	throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_NO_SYNC, he);
        }
        catch (org.hibernate.exception.ConstraintViolationException cex) {
        	tx.rollback();
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
    			cex.toString());
        	throw new ExtTableException(cex.getConstraintName(), extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_DB_CONSTRAINT_VIOLATION, cex);
        }
        catch (ClassCastException clEx) {
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
       			clEx.toString());
           	throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_INCORRECT_EXT_FIELD_TYPE, clEx);
        }
        catch (Exception ex) {
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
       
        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Update the value of an extension field. This is the version used by
	 * a transaction with a lot of update operations.
	 * @param extField ExtFieldOT, extended field to update.
	 * @param session Session, Hibernate session opened for the whole set
	 * of update operations.
	 * @param tx Transaction, Transaction that controls the whole set of
	 * update operations.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void saveExtendedFieldValue(ExtFieldOT extField, Session session, Transaction tx) throws ExtTableException {

        try {
        	// If the extension field is of String type --> 
        	// Set the value of the StringValueOT and save it in the database
        	if (extField != null && extField.getFieldType() != null && 
        		extField.getFieldType().intValue() == InterfaceExtTable.EXT_FIELD_TYPE_STRING) {

        		System.out.println("Getting string value for the extension field: " + extField.getIdExtField() + " - " + extField.getIdValue());
        		ExtFieldStringValueOT value = (ExtFieldStringValueOT)session.get(ExtFieldStringValueOT.class, 
        				new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		
        		// If there is no instance of the string value yet --> Create a new one to insert into DB
        		if (value == null) {
        			value = new ExtFieldStringValueOT();
        			value.setIdExtField(extField.getIdExtField());
        			value.setIdValue(extField.getIdValue());
        			value.setId(new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		}
        		
        		if (value != null)
        			value.setValue((String)extField.getValue());
        		session.saveOrUpdate(value);
        		System.out.println("Update done successfully!");
        	}
        	// If the extension field is of Numeric type --> 
        	// Set the value of the NumberValueOT and save it in the database
        	else if (extField != null && extField.getFieldType() != null &&
        		extField.getFieldType().intValue() == InterfaceExtTable.EXT_FIELD_TYPE_NUMERIC) {
        		
        		System.out.println("Getting numeric value for the extension field: " + extField.getIdExtField() + " - " + extField.getIdValue());
        		ExtFieldNumberValueOT value = (ExtFieldNumberValueOT)session.get(ExtFieldNumberValueOT.class, 
        				new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		
        		// If there is no instance of the numeric value yet --> Create a new one to insert into DB
        		if (value == null) {
        			value = new ExtFieldNumberValueOT();
        			value.setIdExtField(extField.getIdExtField());
        			value.setIdValue(extField.getIdValue());
        			value.setId(new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		}
        		
        		if (value != null)
        			value.setValue((BigDecimal)extField.getValue());
        		session.saveOrUpdate(value);
        		System.out.println("Update done successfully!");
        	}
        	// If the extension field is of Date type --> 
        	// Set the value of the DateValueOT and save it in the database
        	else if (extField != null && extField.getFieldType() != null &&
        		extField.getFieldType().intValue() == InterfaceExtTable.EXT_FIELD_TYPE_DATE) {
        		
        		System.out.println("Getting date value for the extension field: " + extField.getIdExtField() + " - " + extField.getIdValue());
        		ExtFieldDateValueOT value = (ExtFieldDateValueOT)session.get(ExtFieldDateValueOT.class, 
        				new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        	
        		// If there is no instance of the date value yet --> Create a new one to insert into DB
        		if (value == null) {
        			value = new ExtFieldDateValueOT();
        			value.setIdExtField(extField.getIdExtField());
        			value.setIdValue(extField.getIdValue());
        			value.setId(new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		}
        		
        		if (value != null)
        			value.setValue((Date)extField.getValue());
        		session.saveOrUpdate(value);
        		System.out.println("Update done successfully!");
        	}
        	// If the extension field is of Boolean type --> 
        	// Set the value of the BooleanValueOT and save it in the database
        	else if (extField != null && extField.getFieldType() != null &&
        		extField.getFieldType().intValue() == InterfaceExtTable.EXT_FIELD_TYPE_BOOLEAN) {
        		
        		System.out.println("Getting boolean value for the extension field: " + extField.getIdExtField() + " - " + extField.getIdValue());
        		ExtFieldBooleanValueOT value = (ExtFieldBooleanValueOT)session.get(ExtFieldBooleanValueOT.class, 
        				new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		
        		// If there is no instance of the boolean value yet --> Create a new one to insert into DB
        		if (value == null) {
        			value = new ExtFieldBooleanValueOT();
        			value.setIdExtField(extField.getIdExtField());
        			value.setIdValue(extField.getIdValue());
        			value.setId(new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		}
        		
        		if (value != null)
        			value.setValue((Boolean)extField.getValue());
        		session.saveOrUpdate(value);
        		System.out.println("Update done successfully!");
        	}
        	// If the extension field is of LongValue type --> 
        	// Set the value of the LongValueOT and save it in the database
        	else if (extField != null && extField.getFieldType() != null &&
        		extField.getFieldType().intValue() == InterfaceExtTable.EXT_FIELD_TYPE_LONGVALUE) {
        		
        		System.out.println("Getting long value for the extension field: " + extField.getIdExtField() + " - " + extField.getIdValue());
/*        		ExtFieldLongValueOT value = (ExtFieldLongValueOT)session.get(ExtFieldLongValueOT.class, 
        				new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        				
        		// If there is no instance of the long value yet --> Create a new one to insert into DB
        		if (value == null) {
        			value = new ExtFieldLongValueOT();
        			value.setIdExtField(extField.getIdExtField());
        			value.setIdValue(extField.getIdValue());
        			value.setId(new ExtFieldValuePK(extField.getIdExtField(), extField.getIdValue()));
        		}
        		
        		if (value != null)
        			value.setValue((Blob)extField.getValue());
        		session.saveOrUpdate(value);
*/
        		System.out.println("Update done successfully!");
        	}
        	// If the extension field is of wrong type --> 
        	// Rollback the transaction and return error message
        	else {
        		System.out.println("An incorrect value type is tried to be updated!");
        		throw new ExtTableException(null, 
        				extField != null?extField.getCodExtField():null, 
        				extField != null?extField.getIdValue():-1, 
        				InterfaceErrors.ERR_INCORRECT_EXT_FIELD_TYPE);
        	}
        }
        catch (org.hibernate.StaleStateException he) {
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
    			he.toString());
          	throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_NO_SYNC, he);
        }
        catch (org.hibernate.exception.ConstraintViolationException cex) {
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
    			cex.toString());
        	throw new ExtTableException(cex.getConstraintName(), extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_DB_CONSTRAINT_VIOLATION, cex);
        }
        catch (ClassCastException clEx) {
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
       			clEx.toString());
           	throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_INCORRECT_EXT_FIELD_TYPE, clEx);
        }
        catch (Exception ex) {
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, extField.getCodExtField(), extField.getIdValue(), InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
	}
	
	/**
	 * Update the extension values of an extended item.
	 * @param lExtFields ArrayList, list of the extension fields that will 
	 * be updated for the extended item.
	 * @param idItem long, internal id of the extended item. 
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void saveExtendedItemValues(ArrayList lExtFields, long idItem) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database update operation.
        Transaction tx = session.beginTransaction();

        for (int i=0; lExtFields != null && i < lExtFields.size(); i++) {
        	// Get the next extension field in the list
        	ExtFieldOT extField = (ExtFieldOT)lExtFields.get(i);
        	
        	// Set the id of the original item
    		if (extField != null)
    			extField.setIdValue(idItem);
    		
    		// Update the extension field value in the DB
	        try {
	        	this.saveExtendedFieldValue(extField, session, tx);
	        } catch (ExtTableException ex) {
	        	// In case any error took place during updating transaction must be rolled back
	        	tx.rollback();
	        	session.close();
	        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
	    			ex.toString());
	          	throw ex;
	        } // try-catch
        } // for each extension field
        
        // If every field was updated correctly --> Commit the transaction
        tx.commit();
        
        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Update the extension values of an extended item.
	 * @param mExtFields Map, set of the extension fields that will 
	 * be updated for the extended item.
	 * @param idItem long, internal id of the extended item. 
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void saveExtendedItemValues(Map mExtFields, long idItem) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database update operation.
        Transaction tx = session.beginTransaction();

        Iterator it = null;
        try {
        	it = mExtFields.keySet().iterator();
        }
        catch (Exception ex) {
        	System.out.println("The map of extension fields is empty!");
        	// Error has taken place --> Rollback the transaction and close the session
        	tx.rollback();
        	session.close();

        	throw new ExtTableException(null, null, idItem, InterfaceErrors.ERR_EXT_FIELD_LIST_EMPTY);
        }
        
        while (it != null && it.hasNext()) {
        	// Get the next extension field in the list
        	ExtFieldOT extField = (ExtFieldOT)mExtFields.get(it.next());
        	
        	// Set the id of the original item
    		if (extField != null)
    			extField.setIdValue(idItem);
    		
    		// Update the extension field value in the DB
	        try {
	        	this.saveExtendedFieldValue(extField, session, tx);
	        } catch (ExtTableException ex) {
	        	// In case any error took place during updating transaction must be rolled back
	        	tx.rollback();
	        	session.close();
	        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be updated in DB! \n" +
	    			ex.toString());
	          	throw ex;
	        } // try-catch
        } // for each extension field
        
        // If every field was updated correctly --> Commit the transaction
        tx.commit();
        
        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Delete the string value of an extension field. This is the version used by
	 * a transaction with a lot of delete operations.
	 * @param extFieldValue ExtFieldStringValueOT, extended field string value 
	 * to delete.
	 * @param session Session, Hibernate session opened for the whole set
	 * of delete operations.
	 * @param tx Transaction, Transaction that controls the whole set of
	 * delete operations.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void deleteExtendedFieldValue(ExtFieldStringValueOT extFieldValue, Session session, Transaction tx) throws ExtTableException {

        try {
    		session.delete(extFieldValue);
    		System.out.println("Delete done successfully!");
        }
        catch (HibernateException ex) {
        	System.err.println("The extension field string value: " + extFieldValue.getIdExtField() + " couldn't be deleted from DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, "" + extFieldValue.getIdExtField(), extFieldValue.getIdValue(), InterfaceErrors.ERR_NO_EXT_FIELD_FOUND, ex);
        }
	}
	
	/**
	 * Delete the numeric value of an extension field. This is the version used by
	 * a transaction with a lot of delete operations.
	 * @param extFieldValue ExtFieldNumberValueOT, extended field numeric value 
	 * to delete.
	 * @param session Session, Hibernate session opened for the whole set
	 * of delete operations.
	 * @param tx Transaction, Transaction that controls the whole set of
	 * delete operations.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void deleteExtendedFieldValue(ExtFieldNumberValueOT extFieldValue, Session session, Transaction tx) throws ExtTableException {

        try {
    		session.delete(extFieldValue);
    		System.out.println("Delete done successfully!");
        }
        catch (HibernateException ex) {
        	System.err.println("The extension field numeric value: " + extFieldValue.getIdExtField() + " couldn't be deleted from DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, "" + extFieldValue.getIdExtField(), extFieldValue.getIdValue(), InterfaceErrors.ERR_NO_EXT_FIELD_FOUND, ex);
        }
	}
	
	/**
	 * Delete the numeric value of an extension field. This is the version used by
	 * a transaction with a lot of delete operations.
	 * @param extFieldValue ExtFieldBooleanValueOT, extended field boolean value 
	 * to delete.
	 * @param session Session, Hibernate session opened for the whole set
	 * of delete operations.
	 * @param tx Transaction, Transaction that controls the whole set of
	 * delete operations.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void deleteExtendedFieldValue(ExtFieldBooleanValueOT extFieldValue, Session session, Transaction tx) throws ExtTableException {

        try {
    		session.delete(extFieldValue);
    		System.out.println("Delete done successfully!");
        }
        catch (HibernateException ex) {
        	System.err.println("The extension field boolean value: " + extFieldValue.getIdExtField() + " couldn't be deleted from DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, "" + extFieldValue.getIdExtField(), extFieldValue.getIdValue(), InterfaceErrors.ERR_NO_EXT_FIELD_FOUND, ex);
        }
	}
	
	/**
	 * Delete the numeric value of an extension field. This is the version used by
	 * a transaction with a lot of delete operations.
	 * @param extFieldValue ExtFieldDateValueOT, extended field date value 
	 * to delete.
	 * @param session Session, Hibernate session opened for the whole set
	 * of delete operations.
	 * @param tx Transaction, Transaction that controls the whole set of
	 * delete operations.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void deleteExtendedFieldValue(ExtFieldDateValueOT extFieldValue, Session session, Transaction tx) throws ExtTableException {

        try {
    		session.delete(extFieldValue);
    		System.out.println("Delete done successfully!");
        }
        catch (HibernateException ex) {
        	System.err.println("The extension field date value: " + extFieldValue.getIdExtField() + " couldn't be deleted from DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, "" + extFieldValue.getIdExtField(), extFieldValue.getIdValue(), InterfaceErrors.ERR_NO_EXT_FIELD_FOUND, ex);
        }
	}
	
	/**
	 * Delete the numeric value of an extension field. This is the version used by
	 * a transaction with a lot of delete operations.
	 * @param extFieldValue ExtFieldLongValueOT, extended field long value 
	 * to delete.
	 * @param session Session, Hibernate session opened for the whole set
	 * of delete operations.
	 * @param tx Transaction, Transaction that controls the whole set of
	 * delete operations.
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
/*	public void deleteExtendedFieldValue(ExtFieldLongValueOT extFieldValue, Session session, Transaction tx) throws ExtTableException {

        try {
        		session.delete(extFieldValue);
        		System.out.println("Delete done successfully!");
        }
        catch (HibernateException ex) {
        	System.err.println("The extension field long value: " + extFieldValue.getIdExtField() + " couldn't be deleted from DB! \n" +
          		ex.toString());
            throw new ExtTableException(null, "" + extFieldValue.getIdExtField(), extFieldValue.getIdValue(), InterfaceErrors.ERR_NO_EXT_FIELD_FOUND, ex);
        }
	}
*/
	
	/**
	 * Delete the extension field values of an extended item.
	 * @param codExtTable String, External code of the extension table. 
	 * @param idItem long, internal id of the extended item. 
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void deleteExtendedItemValues(String codExtTable, long idItem) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database delete operation.
        Transaction tx = session.beginTransaction();

        // Build the SQL queries
        String sql[] = {
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_STRING_VALUE_TABLE_NAME, true),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_BOOLEAN_VALUE_TABLE_NAME, true),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_NUMBER_VALUE_TABLE_NAME, true),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_DATE_VALUE_TABLE_NAME, true),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_LONG_VALUE_TABLE_NAME, true)
        };
    		
		// Run the SQL queries to delete the extension field values from the DB
        int result = 0;
        int i = 0;
        try {
        	for (i=0; i<sql.length; i++) {
        		SQLQuery sqlQuery = session.createSQLQuery(sql[i]);
        		sqlQuery.setLong(0, idItem);
        		sqlQuery.setString(1, codExtTable);
        		result += sqlQuery.executeUpdate();
        	} // for
        } catch (HibernateException ex) {
        	// In case any error took place during deleting transaction must be rolled back
        	tx.rollback();
        	session.close();
        	System.err.println("The extension field values of type: " + InterfaceExtTable.EXT_FIELD_TYPES[i] + " couldn't be deleted from DB! \n" +
    			ex.toString());
          	throw new ExtTableException(codExtTable, null, idItem, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        } // try-catch
        
        // If every field was deleted correctly --> Commit the transaction
        tx.commit();
        System.out.println(result + " extension field values have been deleted succesfully!");

        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Delete the extension field definition and all the values stored for it. 
	 * @param extField String, ExtFieldOT extension field to delete.
	 * @throws ExtTableException, in case any error take place during
	 * the delete operation.
	 */
	public void deleteExtensionField(ExtFieldOT extField) throws ExtTableException {
		// If the extField is null or has no id --> The extField doesn't exist in the DB
		if (extField == null || extField.getIdExtField() <= 0) {
			// Throw exception with the error
			throw new ExtTableException(null, extField != null?extField.getCodExtField():null, -1, InterfaceErrors.ERR_DELETE_NON_EXISTENT_EXT_FIELD);
		}
		
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database delete operation.
        Transaction tx = session.beginTransaction();

        // Run the SQL queries to delete from the DB the extension values 
        // belonging to the extension field
        try {
        	this.deleteAllExtensionFieldValues(extField.getCodExtField(), session, tx);
        }
        catch (HibernateException ex) {
        	// In case any error took place during deleting transaction must be rolled back
        	tx.rollback();
        	session.close();
        	System.err.println("The values defined for the extension field: " + extField.getCodExtField() + " couldn't be deleted from DB! \n" +
    			ex.toString());
          	throw new ExtTableException(null, extField.getCodExtField(), -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
        
		// Run the SQL queries to delete the extension field values from the DB
        int result = 0;
        try {
        	session.delete(extField);
        } catch (HibernateException ex) {
        	// In case any error took place during deleting transaction must be rolled back
        	tx.rollback();
        	session.close();
        	System.err.println("The extension field: " + extField.getCodExtField() + " couldn't be deleted from DB! \n" +
    			ex.toString());
          	throw new ExtTableException(null, extField.getCodExtField(), -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        } // try-catch
        
        // If the field was deleted correctly --> Commit the transaction
        tx.commit();
        System.out.println("The extension field (" + extField.getCodExtField() + ") has been deleted succesfully!");

        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Delete the extension field values of all the extension fields belonging
	 * to the extension table (including all the values for all the extended items).
	 * @param codExtTable String, External code of the extension table. 
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void deleteAllExtensionTableValues(String codExtTable) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database delete operation.
        Transaction tx = session.beginTransaction();

		// Run the SQL queries to delete the extension field values from the DB
        int result = 0;
        try {
        	result = this.deleteAllExtensionTableValues(codExtTable, session, tx);
        }
        catch (ExtTableException ex) {
        	System.err.println("Extension fields values couldn't be deleted from DB!");
        	tx.rollback();
        	session.close();
        	throw new ExtTableException(codExtTable, null, -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
                
        // If every field was deleted correctly --> Commit the transaction
        tx.commit();
        System.out.println(result + " extension field values have been deleted succesfully!");

        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Delete the extension field values of all the extension fields belonging
	 * to the extension table (including all the values for all the extended items).
	 * @param codExtTable String, External code of the extension table.
	 * @param session Session
	 * @param tx Transaction
	 * @return int, number of rows deleted in DB. 
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	private int deleteAllExtensionTableValues(String codExtTable, Session session, Transaction tx) throws ExtTableException {
        // Build the SQL queries
        String sql[] = {
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_STRING_VALUE_TABLE_NAME, false),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_BOOLEAN_VALUE_TABLE_NAME, false),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_NUMBER_VALUE_TABLE_NAME, false),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_DATE_VALUE_TABLE_NAME, false),
        		this.buildDeleteValuesSQL(InterfaceExtTable.EXT_FIELD_LONG_VALUE_TABLE_NAME, false)
        };
    		
		// Run the SQL queries to delete the extension field values from the DB
        int result = 0;
        int i = 0;
        try {
        	for (i=0; i<sql.length; i++) {
        		SQLQuery sqlQuery = session.createSQLQuery(sql[i]);
        		sqlQuery.setString(0, codExtTable);
        		result += sqlQuery.executeUpdate();
        	} // for
        } catch (HibernateException ex) {
          	throw new ExtTableException(codExtTable, null, -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        } // try-catch
        
        return result;
	}
	
	/**
	 * Delete the extension field values (including all the extension 
	 * field values for all the extended items).
	 * @param codExtField String, External code of the extension field. 
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	public void deleteAllExtensionFieldValues(String codExtField) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database delete operation.
        Transaction tx = session.beginTransaction();

		// Run the SQL queries to delete the extension field values from the DB
        int result = 0;
        try {
        	result = this.deleteAllExtensionFieldValues(codExtField, session, tx);
        }
        catch (ExtTableException ex) {
        	System.err.println("Extension fields values couldn't be deleted from DB!");
        	tx.rollback();
        	session.close();
        	throw new ExtTableException(null, codExtField, -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
                
        // If every field was deleted correctly --> Commit the transaction
        tx.commit();
        System.out.println(result + " extension field values have been deleted succesfully!");

        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Delete the extension field values (including all the extension 
	 * field values for all the extended items).
	 * @param codExtField String, External code of the extension field.
	 * @param session Session
	 * @param tx Transaction
	 * @return int, number of rows deleted in DB. 
	 * @throws ExtTableException, in case any error take place during
   	 * the query operation. 
	 */
	private int deleteAllExtensionFieldValues(String codExtField, Session session, Transaction tx) throws ExtTableException {
        // Build the SQL queries
        String sql[] = {
        		this.buildDeleteFieldValuesSQL(InterfaceExtTable.EXT_FIELD_STRING_VALUE_TABLE_NAME),
        		this.buildDeleteFieldValuesSQL(InterfaceExtTable.EXT_FIELD_BOOLEAN_VALUE_TABLE_NAME),
        		this.buildDeleteFieldValuesSQL(InterfaceExtTable.EXT_FIELD_NUMBER_VALUE_TABLE_NAME),
        		this.buildDeleteFieldValuesSQL(InterfaceExtTable.EXT_FIELD_DATE_VALUE_TABLE_NAME),
        		this.buildDeleteFieldValuesSQL(InterfaceExtTable.EXT_FIELD_LONG_VALUE_TABLE_NAME)
        };
    		
		// Run the SQL queries to delete the extension field values from the DB
        int result = 0;
        int i = 0;
        try {
        	for (i=0; i<sql.length; i++) {
        		SQLQuery sqlQuery = session.createSQLQuery(sql[i]);
        		sqlQuery.setString(0, codExtField);
        		result += sqlQuery.executeUpdate();
        	} // for
        } catch (HibernateException ex) {
          	throw new ExtTableException(null, codExtField, -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        } // try-catch
        
        return result;
	}
	
	/**
	 * Update or add a new extension table definition.
	 * @param extTable ExtTableOT, extended table to update/add.
	 * @throws ExtTableException, in case any error take place during
   	 * the update operation. 
	 */
	public void saveExtensionTableDefinition(ExtTableOT extTable) throws ExtTableException {
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database update operation.
        Transaction tx = session.beginTransaction();
        
        // If the extension table is null --> Return error message
        if (extTable == null || extTable.getCodExtTable() == null) {
            System.out.println("A null extension table is tried to be updated!");
            throw new ExtTableException(null, null, -1, InterfaceErrors.ERR_SAVE_NULL_EXT_TABLE);
        }

        try {
        	session.saveOrUpdate(extTable);
        	System.out.println("Update done successfully!");
        	tx.commit();
        	session.flush();
        }
        catch (org.hibernate.StaleStateException he) {
        	tx.rollback();
        	System.err.println("The extension table: " + extTable.getCodExtTable() + " couldn't be updated in DB! \n" +
    			he.toString());
          	throw new ExtTableException(extTable.getCodExtTable(), null, -1, InterfaceErrors.ERR_NO_SYNC, he);
        }
        catch (org.hibernate.exception.ConstraintViolationException cex) {
        	tx.rollback();
        	System.err.println("The extension table: " + extTable.getCodExtTable() + " couldn't be updated in DB! \n" +
        			cex.toString());
              	throw new ExtTableException(extTable.getCodExtTable(), null, -1, InterfaceErrors.ERR_DB_CONSTRAINT_VIOLATION, cex);
        }
        catch (Exception ex) {
        	System.err.println("The extension table: " + extTable.getCodExtTable() + " couldn't be updated in DB! \n" +
        			ex.toString());
              	throw new ExtTableException(extTable.getCodExtTable(), null, -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }
       
        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Delete the extension table definition and all the fields and
	 * values stored for it. 
	 * @param extTable ExtTableOT, extension table to delete.
	 * @throws ExtTableException, in case any error take place during
	 * the delete operation.
	 */
	public void deleteExtensionTableDefinition(ExtTableOT extTable) throws ExtTableException {
		// If the extTable is null or has no id --> The extTable doesn't exist in the DB
		if (extTable == null || extTable.getIdExtTable() <= 0) {
			// Throw exception with the error
			throw new ExtTableException(extTable != null?extTable.getCodExtTable():null, null, -1, InterfaceErrors.ERR_DELETE_NON_EXISTENT_EXT_TABLE);
		}
		
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
	
		// Open a new transaction to do the database delete operation.
        Transaction tx = session.beginTransaction();
        
        // Run the SQL queries to delete from the DB the extension values 
        // belonging to the extension table
        try {
        	this.deleteAllExtensionTableValues(extTable.getCodExtTable(), session, tx);
        }
        catch (HibernateException ex) {
        	// In case any error took place during deleting transaction must be rolled back
        	tx.rollback();
        	session.close();
        	System.err.println("The values defined for the extension table: " + extTable.getCodExtTable() + " couldn't be deleted from DB! \n" +
    			ex.toString());
          	throw new ExtTableException(extTable.getCodExtTable(), null, -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        }

		// Run the SQL queries to delete the extension table from the DB
        try {
        	session.delete(extTable);
        } catch (HibernateException ex) {
        	// In case any error took place during deleting transaction must be rolled back
        	tx.rollback();
        	session.close();
        	System.err.println("The extension table: " + extTable.getCodExtTable() + " couldn't be deleted from DB! \n" +
    			ex.toString());
          	throw new ExtTableException(extTable.getCodExtTable(), null, -1, InterfaceErrors.ERR_DB_QUERY_ERROR, ex);
        } // try-catch
        
        // If the table was deleted correctly --> Commit the transaction
        tx.commit();
        System.out.println("Extension table (" + extTable.getCodExtTable() + ") has been deleted succesfully!");

        // Close the Hibernate session
        session.close();
	}
	
	/**
	 * Return the database field names of the view of extended items.
	 * @return ArrayList, list of database field names of the view of 
	 * extended item values.
	 */
	private ArrayList getExtendedItemFields() {
		ArrayList result = new ArrayList();
		
//		result.add("\"" + InterfaceExtTable.EXT_TABLE_ID_FIELDNAME + "\"");
//		result.add("\"" + InterfaceExtTable.EXT_TABLE_CODE_FIELDNAME + "\"");
//		result.add("\"" + InterfaceExtTable.EXT_TABLE_DESCRIPTION_FIELDNAME + "\"");
//		result.add("\"" + InterfaceExtTable.EXT_TABLE_IDFIELDNAME_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_ID_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_TYPE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_LENGTH_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_DESCRIPTION_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_NULLABLE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_HISTORIZABLE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_VALUE_ITEM_ID_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_VALUE_STRINGVALUE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_VALUE_BOOLEANVALUE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_VALUE_NUMBERVALUE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_VALUE_DATEVALUE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_VALUE_LONGVALUE_FIELDNAME + "\"");
		
		return result;
	}
	
	/**
	 * Return the database field names of the table of extension field definitions.
	 * @return ArrayList, list of database field names of the table of 
	 * extension field definitions.
	 */
	private ArrayList getExtensionFieldDefinitionFields() {
		ArrayList result = new ArrayList();
		
		result.add("\"" + InterfaceExtTable.EXT_FIELD_ID_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_TYPE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_LENGTH_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_DESCRIPTION_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_NULLABLE_FIELDNAME + "\"");
		result.add("\"" + InterfaceExtTable.EXT_FIELD_HISTORIZABLE_FIELDNAME + "\"");
		
		return result;
	}
	
	/**
	 * Build a sql statement to delete all the extension fields values. It can
	 * be configured to delete all the values defined for an extended item or
	 * all the values defined for an extension table.
	 * @param valuesTable String, external code of the extension table.
	 * @param filteredByIdItem boolean, true if the statement should delete only
	 * the extension values of an extended item, or false to delete all the values
	 * defined for an extension table.
	 * @return String, SQL statement to delete the extension fields values, null in
	 * case any error take place.
	 */
	private String buildDeleteValuesSQL(String valuesTable, boolean filteredByIdItem) {
		if (valuesTable == null || valuesTable.length() <= 0)
			return null;
		
		StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ");
        sql.append("\"");
        sql.append(valuesTable);
        sql.append("\"");
        sql.append(" WHERE ");
        
        // If the delete query is filtered by idItem --> delete will only affect
        // the values of an extended item, so a criteria must be added to the where clause.
        if (filteredByIdItem) {
	        sql.append("\"");
	        sql.append(InterfaceExtTable.EXT_FIELD_VALUE_ITEM_ID_FIELDNAME);
	        sql.append("\"");
	        sql.append(" = ?");
	        sql.append(" AND ");
        }
        
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_FIELD_ID_FIELDNAME);
        sql.append("\"");
        sql.append(" IN (");
        sql.append("SELECT ");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_FIELD_ID_FIELDNAME);
        sql.append("\"");
        sql.append(" FROM ");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_FIELD_TABLE_NAME);
        sql.append("\"");
        sql.append(" f, ");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_TABLE_NAME);
        sql.append("\"");
        sql.append(" t WHERE t.");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_TABLE_ID_FIELDNAME);
        sql.append("\"");
        sql.append(" = f.");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_TABLE_ID_FIELDNAME);
        sql.append("\"");
        sql.append(" AND t.");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_TABLE_CODE_FIELDNAME);
        sql.append("\"");
        sql.append(" = ?)");
        
        return sql.toString();
	}
	
	/**
	 * Build a sql statement to delete all the extension field values.
	 * @param valuesTable String, external code of the extension table.
	 * @return String, SQL statement to delete the extension field values, 
	 * null in case any error take place.
	 */
	private String buildDeleteFieldValuesSQL(String valuesTable) {
		if (valuesTable == null || valuesTable.length() <= 0)
			return null;
		
		StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ");
        sql.append("\"");
        sql.append(valuesTable);
        sql.append("\"");
        sql.append(" WHERE ");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_FIELD_ID_FIELDNAME);
        sql.append("\"");
        sql.append(" IN (");
        sql.append("SELECT ");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_FIELD_ID_FIELDNAME);
        sql.append("\"");
        sql.append(" FROM ");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_FIELD_TABLE_NAME);
        sql.append("\"");
        sql.append(" f ");
        sql.append(" WHERE f.");
        sql.append("\"");
        sql.append(InterfaceExtTable.EXT_FIELD_CODE_FIELDNAME);
        sql.append("\"");
        sql.append(" = ?)");
        
        return sql.toString();
	}
	
	/**
	 * Returns tag Id assigned to CVS source file.
	 */
	public static String getRevision() {
		return "$Id: ExtTableDAO.java,v 1.1 2008-03-18 12:58:45 mario Exp $";
	}
}
